package com.tansun.tandata.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel POI工具类
 * @author dyc
 * @date 2019年7月16日
 */

public class ExcelUtil {
    
    private Workbook workbook;
    private OutputStream os;
    private static String pattern; //日期格式
    
    public ExcelUtil(Workbook workbook){
        this.workbook = workbook;
    }
    
    public ExcelUtil(InputStream is,String version){
        if("2003".equals(version)){
            workbook = new HSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook(); 
        }
    }
    
    @Override
    public String toString() {
         return ""+ getSheetCount() + " sheets";
    }
    
    public String toString(int sheetIndex){
        return "The "+(sheetIndex + 1)+" sheet. Name: "+getsheetName(sheetIndex)+". "+getRowCount(sheetIndex)+" lines";
    }
    
    public static boolean isExcel(String pathName){
        if(StringUtils.isEmpty(pathName)){
            return false;
        }
        return pathName.endsWith(".xls") || pathName.endsWith(".xlsx");
    }
    
    /**
     * 读取Excel第一页的所有数据
     * @return
     * @throws Exception
     */
    public List<List<String>> read() throws Exception{
        return read(0,0,getRowCount(0)-1);
    }
    
    /**
     * 读取指定sheet页所有数据
     * @param sheetIndex
     * @return
     * @throws Exception
     */
    public List<List<String>> read(int sheetIndex) throws Exception{
        return read(sheetIndex,0,getRowCount(sheetIndex)-1);
    }
    
    /**
     * 读取指定sheet页指定行数据
     * @param sheetIndex
     * @param start
     * @param end
     * @return
     * @throws Exception
     */
    public List<List<String>> read(int sheetIndex,int start,int end) throws Exception{
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        List<List<String>> list = new ArrayList<List<String>>();
        
        if(end > getRowCount(sheetIndex)){
            end = getRowCount(sheetIndex);
        }
        
        int cols = sheet.getRow(0).getLastCellNum();    //第一行总列数
        
        for(int i = start;i <= end; i++ ){
            List<String> rowList = new ArrayList<String>();
            Row row = sheet.getRow(i);
            for(int j = 0 ; j < cols ; j++){
                if(row == null){
                    rowList.add(null);
                    continue;
                }
                rowList.add(getCellValueToString(row.getCell(j)));
            }
            list.add(rowList);
        }
        return list;
    }
    
    /**
     * 将数据写入到Excel默认第一页中，从第一行开始写入
     * @param rowData
     * @return
     * @throws IOException
     */
    public boolean write(List<List<String>> rowData)  throws IOException{
        return write(0,rowData,0);
    }
    
    /**
     * 将数据追加到sheet页最后
     * @param sheetIndex
     * @param rowData
     * @param isAppend  是否追加，true追加，false重置sheet在添加
     * @return
     */
    public boolean write(int sheetIndex, List<List<String>> rowData,boolean isAppend) throws IOException{
        if(isAppend){
            return write(sheetIndex,rowData,getRowCount(sheetIndex));
        } else {
            clearSheet(sheetIndex);
            return write(sheetIndex,rowData,0);
        }
    }
    
    /**
     * 将数据写入到Excel指定sheet页指定开始行中，指定行后面数据向后移动
     * @param i
     * @param rowData
     * @param j
     * @return
     */
    public boolean write(int sheetIndex, List<List<String>> rowData, int startRow) {
         Sheet sheet = workbook.getSheetAt(sheetIndex);
         int dataSize = rowData.size();
         if(getRowCount(sheetIndex) > 0){
             sheet.shiftRows(startRow, getRowCount(sheetIndex), dataSize);
         }
         for(int i = 0 ; i < dataSize ; i++){
             Row row = sheet.createRow(i+startRow);
             for(int j = 0 ; j < rowData.get(i).size(); j++){
                 Cell cell = row.createCell(j);
                 cell.setCellValue(rowData.get(i).get(j) + "");
             }
         }
         return true;
    }
    
    /**
     * 将数据写入到Excel新创建的sheet页
     * @param rowData
     * @param sheetName
     * @param isNewSheet
     * @return
     * @throws IOException
     */
    public boolean write(List<List<String>> rowData,String sheetName,boolean isNewSheet) throws IOException{
        Sheet sheet = null;
        if(isNewSheet){
            sheet = workbook.createSheet(sheetName);
        }else {
            sheet = workbook.createSheet();
        }
        int sheetIndex = workbook.getSheetIndex(sheet);
        return write(sheetIndex,rowData,0);
    }
    
    /**
     * 设置Cell样式
     * @param sheetIndex
     * @param rowIndex
     * @param style
     * @return
     */
    public boolean setStyle(int sheetIndex,int rowIndex,int colIndex,CellStyle style){
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        sheet.autoSizeColumn(colIndex,true);    //设置列宽度自适应
//        sheet.setColumnWidth(colIndex, 4000);
        Cell cell = sheet.getRow(rowIndex).getCell(colIndex);
        cell.setCellStyle(style);
        return true;
    }
    
    /**
     * 设置样式
     * @param type
     *          1 .标题   2.第一行
     * @return
     */
    public CellStyle makeStyle(int type){
        CellStyle style = workbook.createCellStyle();
        
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));//内容样式，设置单元格内容格式是文本
        style.setAlignment(HorizontalAlignment.CENTER); //居中
        
        style.setBorderTop(BorderStyle.THIN);   //边框样式
        style.setBorderRight(BorderStyle.THIN); 
        style.setBorderBottom(BorderStyle.THIN); 
        style.setBorderLeft(BorderStyle.THIN); 
        
        Font font = workbook.createFont();//文本样式
        if(type == 1){
            font.setBold(true);
            font.setFontHeight((short)500);
        }
        
        if(type == 2){
            font.setBold(true);
            font.setFontHeight((short)300);
        }
        style.setFont(font);
        return style;
    }
    
    /**
     * 合并单元格
     * @param sheetIndex
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     */
    public void region(int sheetIndex,int firstRow,int lastRow,int firstCol,int lastCol){
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }
    
    /**
     * 指定行是否为空
     * @param sheetIndex
     * @param rowIndex
     * @return
     * @throws IOException
     */
    public boolean isRowNull(int sheetIndex,int rowIndex) throws IOException{
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        return sheet.getRow(rowIndex) == null;
    }
    
    /**
     * 创建行，若存在则清空
     * @param sheetIndex
     * @param rowIndex
     * @return
     */
    public boolean createRow(int sheetIndex,int rowIndex) throws IOException{
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        sheet.createRow(rowIndex);
        return true;
    }
    
    /**
     * 指定单元格是否为空
     * @param sheetIndex
     * @param rowIndex
     * @param colIndex
     * @return
     */
    public boolean isCellNull(int sheetIndex,int rowIndex,int colIndex) throws IOException{
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if(!isRowNull(sheetIndex, rowIndex)){
            return false;
        }
        Row row = sheet.getRow(rowIndex);
        return row.getCell(colIndex) == null;
     }
    
    /**
     * 创建单元格
     * @param sheetIndex
     * @param rowIndex
     * @param colIndex
     * @return
     * @throws IOException
     */
    public boolean createCell(int sheetIndex,int rowIndex,int colIndex) throws IOException{
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        row.createCell(colIndex);
        return true;
    }

    /**
     * 转换单元格的类型为String 默认的 <br>
     * 默认的数据类型：CELL_TYPE_BLANK(3), CELL_TYPE_BOOLEAN(4),
     * CELL_TYPE_ERROR(5),CELL_TYPE_FORMULA(2), CELL_TYPE_NUMERIC(0),
     * CELL_TYPE_STRING(1)
     * @param cell
     * @return
     */
    public static String getCellValueToString(Cell cell) {
        String strCell = "";
        if (cell == null) {
            return null;
        }
        // 单元格横向合并
        CellRangeAddress cellRangeAddress = getCellRangeAddress(cell.getSheet(), cell.getRowIndex(), cell.getColumnIndex());
        if(cellRangeAddress != null) {
        	if(cellRangeAddress.getFirstRow() != cellRangeAddress.getLastRow()) {	//纵向合并单元格
        		int firstColumn = cellRangeAddress.getFirstColumn();
                int firstRow = cellRangeAddress.getFirstRow();
                Row fRow = cell.getSheet().getRow(firstRow);
                cell = fRow.getCell(firstColumn);
        	}
//          横向合并的单元格不处理，按原值返回
        }
        
        switch (cell.getCellType()) {
	        case Cell.CELL_TYPE_BOOLEAN:
	            strCell = String.valueOf(cell.getBooleanCellValue());
	            break;
	        case Cell.CELL_TYPE_NUMERIC:
	            if (HSSFDateUtil.isCellDateFormatted(cell)) {
	                Date date = cell.getDateCellValue();
	                if (pattern != null) {
	                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	                    strCell = sdf.format(date);
	                } else {
	                    strCell = date.toString();
	                }
	                break;
	            }
	            // 不是日期格式，则防止当数字过长时以科学计数法显示
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	            strCell = cell.toString();
	            break;
	        case Cell.CELL_TYPE_STRING: 
	            strCell = cell.getStringCellValue();
	            break;        
	        default:
	            break;
        }
        return strCell;
    }

    
  //获取单元格各类型值，返回字符串类型
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        switch (cellType) {
        case Cell.CELL_TYPE_NUMERIC: // 数字
            short format = cell.getCellStyle().getDataFormat();
            if (DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = null;  
                //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                if (format == 20 || format == 32) {  
                    sdf = new SimpleDateFormat("HH:mm");  
                } else if (format == 14 || format == 31 || format == 57 || format == 58) {  
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
                    double value = cell.getNumericCellValue();  
                    Date date = org.apache.poi.ss.usermodel.DateUtil  
                            .getJavaDate(value);  
                    cellValue = sdf.format(date);  
                }else {// 日期  
                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
                }  
                try {
                    cellValue = sdf.format(cell.getDateCellValue());// 日期
                } catch (Exception e) {
                    try {
                        throw new Exception("exception on get date data !".concat(e.toString()));
                    } catch (Exception e1) {
                        e1.printStackTrace();
                    }
                }finally{
                    sdf = null;
                }
            }  else {
                BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); 
                cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
            }
            break;
        case Cell.CELL_TYPE_STRING: // 字符串
            cellValue = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN: // Boolean
            cellValue = cell.getBooleanCellValue()+"";;
            break;
        case Cell.CELL_TYPE_FORMULA: // 公式
            cellValue = cell.getCellFormula();
            break;
        case Cell.CELL_TYPE_BLANK: // 空值
            cellValue = "";
            break;
        case Cell.CELL_TYPE_ERROR: // 故障
            cellValue = "ERROR VALUE";
            break;
        default:
            cellValue = "UNKNOW VALUE";
            break;
        }
        return cellValue;
    }
    
    
    /**
     * 返回sheet中的行数
     * @param sheetIndex
     * @return
     */
    public int getRowCount(int sheetIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if(sheet.getPhysicalNumberOfRows() == 0){
            return 0;
        }
        return sheet.getLastRowNum() + 1;
    }
    
    /**
     * 获取合并单元格
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static CellRangeAddress getCellRangeAddress(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return range;
                }
            }
        }
        return null;
    }
    
    /**
     * 按坐标轴取值
     * @param sheet
     * @param coordinate 坐标 格式=多个大写字母+数字 如A1,AA21
     */
    public static Cell getCellByCoordinate(Sheet sheet,String coordinate) {
    	//校验坐标格式
    	String regEx = "^([A-Z]+)\\d+$";
        Pattern pattern = Pattern.compile(regEx);
        Matcher matcher = pattern.matcher(coordinate);
    	if(matcher.matches()) {	//匹配成功，转化坐标
    		Map<String,Integer> xyAxis = getAxis(coordinate);
    		int x = xyAxis.get("X");
    		int y = xyAxis.get("Y");
    		return sheet.getRow(y).getCell(x);
    	}
    	return null;
    }
    
    private static Map<String,Integer> getAxis(String coordinate){
    	int index = 0;
    	int x = 0;
    	int y = 0;
    	for(;index < coordinate.length(); index++) {
    		char charAt = coordinate.charAt(index);
    		byte b = (byte)charAt;
    		if(65 <= b && b <= 90) {	//字母
    			x += b ;
    		} else {
    			break;
    		}
    	}
    	x = x - 65;
    	y = Integer.parseInt(coordinate.substring(index)) - 1;
    	Map<String, Integer> xyAxis = new HashMap<>();
    	xyAxis.put("X", x);
    	xyAxis.put("Y", y);
    	return xyAxis;
    }
    
    /**
     * 返回所在行的列数
     * @param sheetIndex
     * @param rowIndex
     * @return
     */
    public int getColumnCount(int sheetIndex,int rowIndex){
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        return row == null ? -1 : row.getLastCellNum();
    }
    
    /**
     * 设置某行某单元格的值
     * @param sheetIndex
     * @param rowIndex
     * @param colIndex
     * @param value
     * @return
     * @throws IOException
     */
    public boolean setValue(int sheetIndex,int rowIndex,int colIndex,String value) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        sheet.getRow(rowIndex).getCell(colIndex).setCellValue(value);
        return true;
    }
    
    /**
     *返回某行某单元格的值
     * @param sheetIndex
     * @param rowIndex
     * @param colIndex
     * @return
     */
    public String getValueAt(int sheetIndex,int rowIndex,int colIndex){
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        return getCellValueToString(sheet.getRow(rowIndex).getCell(colIndex));
    }
    
    /**
     * 重置指定行的值
     * @param rowData
     *            数据
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @return
     * @throws IOException
     */
    public boolean setRowValue(int sheetIx, List<String> rowData, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        for (int i = 0; i < rowData.size(); i++) {
            row.getCell(i).setCellValue(rowData.get(i));
        }
        return true;
    }
    
    /**
     * 返回指定行的值的集合
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @return
     */
    public List<String> getRowValue(int sheetIx, int rowIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        Row row = sheet.getRow(rowIndex);
        List<String> list = new ArrayList<String>();
        if (row == null) {
            list.add(null);
        } else {
            for (int i = 0; i < row.getLastCellNum(); i++) {
                list.add(getCellValueToString(row.getCell(i)));
            }
        }
        return list;
    }
    
    /**
     * 返回列的值的集合
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @param colIndex
     *            指定列，从0开始
     * @return
     */
    public List<String> getColumnValue(int sheetIx, int rowIndex, int colIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        List<String> list = new ArrayList<String>();
        for (int i = rowIndex; i < getRowCount(sheetIx); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                list.add(null);
                continue;
            }
            list.add(getCellValueToString(sheet.getRow(i).getCell(colIndex)));
        }
        return list;
    }
    
    public String getsheetName(int sheetIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        return sheet.getSheetName();
    }

    public int getSheetCount() {
        return workbook.getNumberOfSheets();
    }

    public void setPattern(String pattern){
        this.pattern = pattern;
    }
    
    /**
     * 获取sheet的索引，从0开始
     * @param name
     *            sheet 名称
     * @return -1表示该未找到名称对应的sheet
     */
    public int getSheetIndex(String name) {
        return workbook.getSheetIndex(name);
    }
    
    /**
     * 删除指定sheet
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @return
     * @throws IOException
     */
    public boolean removeSheetAt(int sheetIx) throws IOException {
        workbook.removeSheetAt(sheetIx);
        return true;
    }

    /**
     * 删除指定sheet中行，改变该行之后行的索引
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @param rowIndex
     *            指定行，从0开始
     * @return
     * @throws IOException
     */
    public boolean removeRow(int sheetIx, int rowIndex) throws IOException {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        sheet.shiftRows(rowIndex + 1, getRowCount(sheetIx), -1);
        Row row = sheet.getRow(getRowCount(sheetIx) - 1);
        sheet.removeRow(row);
        return true;
    }

    /**
     * 设置sheet 页的索引
     * @param sheetname
     *            Sheet 名称
     * @param pos
     *            Sheet 索引，从0开始
     */
    public void setSheetOrder(String sheetname, int sheetIx) {
        workbook.setSheetOrder(sheetname, sheetIx);
    }

    /**
     * 清空指定sheet页（先删除后添加并指定sheetIx）
     * @param sheetIx
     *            指定 Sheet 页，从 0 开始
     * @return
     * @throws IOException
     */
    public boolean clearSheet(int sheetIndex) throws IOException {
        String sheetname = getsheetName(sheetIndex);
        removeSheetAt(sheetIndex);
        workbook.createSheet(sheetname);
        setSheetOrder(sheetname, sheetIndex);
        return true;
    }
    
    /**
     * 关闭流
     * @throws IOException
     */
    public void close() throws IOException {
        if (os != null) {
            os.close();
        }
        workbook.close();
    }
    
}
